﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ThanhTu.GNCMedia.KQXS.DataAccess.DO;

namespace ThanhTu.GNCMedia.KQXS.DataAccess.DAL
{
    public class MessengerSyntaxDAL : BaseDAL
    {
        public MessengerSyntaxDAL() : base() { }

        public int add(MessengerSyntaxDO vMessengerSyntaxDO)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "MessengerSyntax_PKG.SaveMessSyntax";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = vMessengerSyntaxDO.ProvinceID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Syntax", OracleDbType.Varchar2);
            oraParam.Value = vMessengerSyntaxDO.Syntax;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Switch_Board", OracleDbType.Varchar2);
            oraParam.Value = vMessengerSyntaxDO.SwitchBoard;
            oraComm.Parameters.Add(oraParam);


            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public int delete(string ProvinceID)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "MessengerSyntax_PKG.DeleteMessSyntax";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = ProvinceID;
            oraComm.Parameters.Add(oraParam);


            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["OUTRETURN"]))
            { result = Convert.ToInt32(oraComm.Parameters["OUTRETURN"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public MessengerSyntaxDO GetByMessSyntax(string Province_ID)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "MessengerSyntax_PKG.GetByMessSyntax";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_Province_ID", OracleDbType.Varchar2);
            oraParam.Value = Province_ID;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataRow dr = null;
            MessengerSyntaxDO vMessengerSyntaxDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                        vMessengerSyntaxDO = new MessengerSyntaxDO();

                        if (!Convert.IsDBNull(dr["PROVINCEID"]))
                        { vMessengerSyntaxDO.ProvinceID = Convert.ToString(dr["PROVINCEID"]); }

                        if (!Convert.IsDBNull(dr["SYNTAX"]))
                        { vMessengerSyntaxDO.Syntax = Convert.ToString(dr["SYNTAX"]); }

                        if (!Convert.IsDBNull(dr["SWITCHBOARD"]))
                        { vMessengerSyntaxDO.SwitchBoard = Convert.ToString(dr["SWITCHBOARD"]); }
                        dr = null;
                    }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vMessengerSyntaxDO;
        }

        public DataTable SelectAll_Messenger(int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "MessengerSyntax_PKG.GetAllMessSyntax";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }

        public DataTable SelectAll_Messenger_Region(string Region_id, int pageIndex, int pageSize, ref int rowCount)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "MessengerSyntax_PKG.GetAllMessSyntax_RegionID";
            OracleParameter oraParam = null;
            oraParam = new OracleParameter("IN_Region_ID", OracleDbType.Varchar2);
            oraParam.Value = Region_id;
            oraComm.Parameters.Add(oraParam);


            oraParam = new OracleParameter("INPAGEINDEX", OracleDbType.Int32);
            oraParam.Value = pageIndex;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("INPAGESIZE", OracleDbType.Int32);
            oraParam.Value = pageSize;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTROWCOUNT", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];

                    if (!Convert.IsDBNull(oraComm.Parameters["OUTROWCOUNT"]))
                    { rowCount = Convert.ToInt32(oraComm.Parameters["OUTROWCOUNT"].Value.ToString()); }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return dt;
        }
    }
}
